package com.sinopac.model.TokenStorage;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.commons.lang.StringUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import com.sinopac.entity.TokenStorage.TokenVO;
import com.sinopac.model.core.ModelCore;
import com.sinopac.util.FileUploadUtil;
import com.sinopac.util.IdUtil;
import com.sinopac.util.PropsUtil;

public class TokenStorageModel extends ModelCore{
	
	public TokenVO getItems(){
		TokenVO vo = new TokenVO();
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_sb = new StringBuffer();
		sql_sb.append("select Count(t.token_esn)   as total_cnt, ");
		sql_sb.append("       Count(tc.related_to) as allotted_cnt ");
		sql_sb.append("from   tokens as t ");
		sql_sb.append("       left join token_customer as tc ");
		sql_sb.append("              on t.token_esn = tc.token_esn ");
		sql_sb.append("where  t.token_status != '03' ");
		dam.setQueryString(sql_sb.toString());
		try {
			List<Map<String,Object>> cnt_list = dam.exeQueryMap();
			Map<String,Object> cntMap = cnt_list.get(0);
			Integer allotted_cnt = (Integer)cntMap.get("allotted_cnt");
			Integer total_cnt = (Integer)cntMap.get("total_cnt");
			dam = new DataAccessManager();
			sql_sb = new StringBuffer();
			sql_sb.append("select Count(*) as block_cnt ");
			sql_sb.append("from   tokens ");
			sql_sb.append("where  token_status = '03' ");
			dam.setQueryString(sql_sb.toString());
			List<Map<String,Object>> list = dam.exeQueryMap();
			Map<String,Object> blockMap = list.get(0);
			Integer block_cnt = (Integer)blockMap.get("block_cnt");
			vo.setTotal_cnt(block_cnt+total_cnt);
			vo.setNotAllotted_cnt(total_cnt-allotted_cnt);
			vo.setAllotted_cnt(allotted_cnt);
			vo.setBlock_cnt(block_cnt);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vo;
	}
	/**
	 * 確定檔案筆數是否正確
	 * true: 正確檔案入庫  false: 錯誤檔案刪除
	 * @param xmlFile
	 * @param userName
	 * @return
	 */
	public Boolean chkFile_status(File xmlFile,String userName){
		boolean flag = false;
		int cnt = 0;
		int total = 0;
		DocumentBuilder builder = null;
		DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();
		try {
			builder = builderFactory.newDocumentBuilder();
			Document document = builder.parse(xmlFile);
			Element root = document.getDocumentElement();
			
			System.out.println(root.getNodeName());
			
			NodeList fileInfoNode = root.getElementsByTagName("BATCH_AMOUNT");
			for(int i = 0;i<fileInfoNode.getLength();i++){
				Node detail = fileInfoNode.item(i);
				if("BATCH_AMOUNT".equals(detail.getNodeName())){
					total = Integer.parseInt(detail.getTextContent());
				}
			}
//			total = Integer.parseInt(node.getTextContent());
			NodeList nodeDetail = root.getElementsByTagName("ESN");
			for(int i = 0;i<nodeDetail.getLength();i++){
				Node detail = nodeDetail.item(i);
				if("ESN".equals(detail.getNodeName())){
					cnt++;
				}
			}
			flag = total == cnt;
			if(flag){
				DataAccessManager dam = new DataAccessManager();
				StringBuffer sql_sb = new StringBuffer();
				sql_sb.append("insert into job_queues ");
				sql_sb.append("            (job_id, ");
				sql_sb.append("             job_code, ");
				sql_sb.append("             batch_file, ");
				sql_sb.append("             batch_amount, ");
				sql_sb.append("             operator, ");
				sql_sb.append("             submitted_time) ");
				sql_sb.append("values      (:job_id, ");
				sql_sb.append("             '3000', ");
				sql_sb.append("             :batch_file, ");
				sql_sb.append("             :batch_amount, ");
				sql_sb.append("             :operator, ");
				sql_sb.append("             Getdate()) ");
				dam.setQueryString(sql_sb.toString());
				dam.setObject("job_id", IdUtil.getJob_id());
				dam.setObject("batch_file", xmlFile.getName());
				dam.setObject("batch_amount", total);
				dam.setObject("operator", userName);
				dam.exeUpdate();
			}else{
				xmlFile.delete();
			}
		} catch (ParserConfigurationException e) {
			e.printStackTrace();
		} catch (SAXException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			logger.error("SQL錯誤-TOKEN入庫-insert Job_queues");
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 主管覆核後寫入DB
	 * @param fileName
	 * @param job_id
	 * @return true 成功  |false  失敗
	 */
	public String insertTokens(String fileName,String job_id){
		StringBuffer sb = new StringBuffer("Token入庫:\r\n");
		File xmlFile = FileUploadUtil.getUploadUrl(fileName);
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_sb = new StringBuffer();
		sql_sb.append(" select token_esn from tokens ");
		dam.setQueryString(sql_sb.toString());
		DocumentBuilder builder = null;
		DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance();
		try {
			List<String> tokenList = dam.batchQuery();
			builder = builderFactory.newDocumentBuilder();
			Document document = builder.parse(xmlFile);
			Element root = document.getDocumentElement();
			NodeList nodeDetail = root.getElementsByTagName("ESN");
			for(int i = 0;i<nodeDetail.getLength();i++){
				Node detail = nodeDetail.item(i);
				if("ESN".equals(detail.getNodeName())){
					String text = "";
					String filename = String.format("%s%s.log", PropsUtil.getConfig("logsFile.path"),job_id);
					File file = new File(PropsUtil.getConfig("logsFile.path"));
					if(!file.exists()){
						file.mkdirs();
					}
					if(tokenList.contains(StringUtils.rightPad(detail.getTextContent(),16," "))){
						text = String.format("%s\tTOKEN已存在，未入資料庫\r\n", detail.getTextContent());
						sb.append(text);
					}else{
						dam = new DataAccessManager();
						sql_sb = new StringBuffer();
						sql_sb.append("insert into tokens (token_esn,token_status,is_receipt_printed,created_time) ");
						sql_sb.append("values (:token_esn,'00','0',getdate())");
						dam.setObject("token_esn", detail.getTextContent());
						dam.setQueryString(sql_sb.toString());
						if(dam.exeUpdate()>0){
							text = String.format("%s\t入庫成功\r\n", detail.getTextContent());
						}else{
							text = String.format("%s\t入庫失敗\r\n", detail.getTextContent());
						}
					}
					writeLogs(text,filename);
				}
			}
			deletejob_queues(job_id);
		} catch (ParserConfigurationException e) {
			logger.error("轉檔錯誤-TOKEN入庫-insertTokens");
			e.printStackTrace();
		} catch (SAXException e) {
			logger.error("轉檔錯誤-TOKEN入庫-insertTokens");
			e.printStackTrace();
		} catch (IOException e) {
			logger.error("轉檔錯誤-TOKEN入庫-insertTokens");
			e.printStackTrace();
		} catch (SQLException e) {
			logger.error("SQL錯誤-TOKEN入庫-insert Tokens");
			e.printStackTrace();
		}		
		return sb.toString();
	}
	
	private int deletejob_queues(String job_id) throws SQLException{
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_sb = new StringBuffer();
		sql_sb.append("DELETE FROM job_queues ");
		sql_sb.append("WHERE  job_id = :job_id ");
		dam.setQueryString(sql_sb.toString());
		dam.setObject("job_id", job_id);
		return dam.exeDelete();
	}
	
	 /**
     * 寫入文字檔(使用FileWriter 寫檔編碼為預設的iso-8859-1)，
     * 因此此method使用OutputStreamWriter寫檔，可自行指定格式
     *
     * @param text        將整個String寫入指定的檔案
     * @param filename    可用相對路徑或絕對路徑
     * @return            true 寫檔成功 | false 寫檔失敗
     */
    private boolean writeLogs(String text,String filename){
        if(text.equals("")){
            return false;
        }
        File file = new File(filename);//建立檔案，準備寫檔
        try{
            BufferedWriter bufWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file,true),"utf8"));
            bufWriter.write(text);
            bufWriter.close();
        }catch(IOException e){
            e.printStackTrace();
            System.out.println(filename + "寫檔發生錯誤");
            return false;
        }
        return true;
    }
}
